{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 第三阶段 - 第3讲：缺失值与异常值处理\n",
    "\n",
    "## 学习目标\n",
    "- 深入理解缺失值的产生原因和影响\n",
    "- 掌握缺失值的检测和可视化方法\n",
    "- 熟练使用多种缺失值处理策略\n",
    "- 掌握异常值的多种检测方法（业务规则、统计法、IQR法）\n",
    "- 学会根据场景选择合适的异常值处理方式\n",
    "- 完成真实数据的清洗实战\n",
    "\n",
    "**重要性**: ⭐⭐⭐⭐⭐ 数据清洗的核心技能！\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入必要的库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from scipy import stats\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "# 设置\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', 100)\n",
    "pd.set_option('display.float_format', '{:.2f}'.format)\n",
    "\n",
    "# 中文显示\n",
    "plt.rcParams['font.sans-serif'] = ['Arial Unicode MS', 'SimHei']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "print(\"✅ 环境配置完成\")\n",
    "print(f\"Pandas版本: {pd.__version__}\")\n",
    "print(f\"Numpy版本: {np.__version__}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、缺失值深度解析\n",
    "\n",
    "### 1.1 什么是缺失值？\n",
    "\n",
    "**定义**: 数据集中某些观测值没有被记录的现象\n",
    "\n",
    "**表现形式**:\n",
    "- Python/Pandas: `NaN` (Not a Number), `None`, `pd.NA`\n",
    "- Excel: 空白单元格\n",
    "- 数据库: `NULL`\n",
    "- 其他: 特殊标记如 `9999`, `-1`, `N/A`, `unknown`\n",
    "\n",
    "### 1.2 缺失值的产生原因\n",
    "\n",
    "1. **数据采集问题**\n",
    "   - 传感器故障\n",
    "   - 网络中断\n",
    "   - 人工录入遗漏\n",
    "\n",
    "2. **用户行为**\n",
    "   - 表单未填写完整\n",
    "   - 拒绝提供某些信息\n",
    "   - 问卷跳题逻辑\n",
    "\n",
    "3. **数据处理**\n",
    "   - 数据合并时匹配不上\n",
    "   - 数据转换错误\n",
    "   - 系统bug\n",
    "\n",
    "4. **数据设计**\n",
    "   - 某些字段本身可选\n",
    "   - 不同版本数据结构不同\n",
    "\n",
    "### 1.3 缺失值的影响\n",
    "\n",
    "❌ **负面影响**:\n",
    "- 统计结果偏差\n",
    "- 模型无法训练\n",
    "- 分析结论错误\n",
    "- 数据量减少\n",
    "\n",
    "✅ **潜在信息**:\n",
    "- 缺失本身可能有意义（如收入不愿透露）\n",
    "- 缺失模式可能揭示问题"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 二、缺失值的检测\n",
    "\n",
    "### 2.1 创建包含缺失值的数据集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建一个真实场景的数据集 - 员工信息\n",
    "np.random.seed(42)\n",
    "n = 50\n",
    "\n",
    "# 基础数据\n",
    "employee_data = {\n",
    "    'employee_id': [f'E{str(i).zfill(4)}' for i in range(1, n+1)],\n",
    "    'name': [f'员工{i}' for i in range(1, n+1)],\n",
    "    'age': np.random.randint(22, 60, n),\n",
    "    'department': np.random.choice(['销售部', '技术部', '市场部', '人力资源部', '财务部'], n),\n",
    "    'position': np.random.choice(['经理', '主管', '专员', '助理'], n),\n",
    "    'salary': np.random.randint(5000, 50000, n),\n",
    "    'years_of_service': np.random.randint(0, 20, n),\n",
    "    'performance_score': np.random.choice([3, 4, 5], n),\n",
    "    'email': [f'employee{i}@company.com' for i in range(1, n+1)],\n",
    "    'phone': [f'138{str(np.random.randint(10000000, 99999999))}' for i in range(n)],\n",
    "    'city': np.random.choice(['北京', '上海', '广州', '深圳', '杭州'], n)\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(employee_data)\n",
    "\n",
    "# 人为引入缺失值（模拟真实场景）\n",
    "\n",
    "# 1. 完全随机缺失 (MCAR - Missing Completely At Random)\n",
    "# 电话号码有10%随机缺失\n",
    "missing_indices = np.random.choice(df.index, size=int(n*0.1), replace=False)\n",
    "df.loc[missing_indices, 'phone'] = np.nan\n",
    "\n",
    "# 2. 随机缺失 (MAR - Missing At Random)\n",
    "# 年龄较大的员工（>50岁）有30%不愿透露邮箱\n",
    "older_employees = df[df['age'] > 50].index\n",
    "missing_email = np.random.choice(older_employees, size=int(len(older_employees)*0.3), replace=False)\n",
    "df.loc[missing_email, 'email'] = np.nan\n",
    "\n",
    "# 3. 非随机缺失 (MNAR - Missing Not At Random)\n",
    "# 高薪员工（>30000）有50%不愿透露薪资\n",
    "high_salary = df[df['salary'] > 30000].index\n",
    "missing_salary = np.random.choice(high_salary, size=int(len(high_salary)*0.5), replace=False)\n",
    "df.loc[missing_salary, 'salary'] = np.nan\n",
    "\n",
    "# 4. 结构性缺失\n",
    "# 新员工（工作年限<1年）没有绩效评分\n",
    "df.loc[df['years_of_service'] < 1, 'performance_score'] = np.nan\n",
    "\n",
    "# 5. 一些随机缺失\n",
    "random_missing = np.random.choice(df.index, size=5, replace=False)\n",
    "df.loc[random_missing, 'city'] = np.nan\n",
    "\n",
    "# 添加一列全是缺失值（测试用）\n",
    "df['bonus'] = np.nan\n",
    "\n",
    "print(\"✅ 创建了包含多种缺失模式的员工数据集\")\n",
    "print(f\"数据规模: {df.shape}\")\n",
    "print(\"\\n前10行数据:\")\n",
    "print(df.head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 基础检测方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 方法1: isnull() / isna() - 检测缺失值（两者等价）\n",
    "print(\"=== 缺失值检测 ===\")\n",
    "print(\"\\n各列缺失值数量:\")\n",
    "print(df.isnull().sum())\n",
    "\n",
    "print(\"\\n各列缺失值占比:\")\n",
    "missing_pct = (df.isnull().sum() / len(df) * 100).round(2)\n",
    "print(missing_pct)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 方法2: info() - 快速查看\n",
    "print(\"\\n=== 使用info()查看缺失情况 ===\")\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 方法3: 缺失值汇总报告\n",
    "def missing_summary(df):\n",
    "    \"\"\"\n",
    "    生成缺失值汇总报告\n",
    "    \"\"\"\n",
    "    total = df.isnull().sum()\n",
    "    percent = (df.isnull().sum() / len(df) * 100)\n",
    "    missing_data = pd.concat([total, percent], axis=1, keys=['总数', '占比(%)'])\n",
    "    missing_data = missing_data[missing_data['总数'] > 0].sort_values('总数', ascending=False)\n",
    "    missing_data['占比(%)'] = missing_data['占比(%)'].round(2)\n",
    "    missing_data['数据类型'] = df.dtypes\n",
    "    return missing_data\n",
    "\n",
    "print(\"\\n=== 缺失值汇总报告 ===\")\n",
    "print(missing_summary(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 方法4: 检查每行的缺失情况\n",
    "print(\"\\n=== 行级缺失统计 ===\")\n",
    "row_missing = df.isnull().sum(axis=1)\n",
    "print(f\"完整行（无缺失）: {(row_missing == 0).sum()} 行\")\n",
    "print(f\"至少1个缺失: {(row_missing > 0).sum()} 行\")\n",
    "print(f\"至少2个缺失: {(row_missing > 1).sum()} 行\")\n",
    "print(f\"最多缺失数: {row_missing.max()} 列\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 缺失值可视化\n",
    "\n",
    "**Excel对比**: Excel需要手动条件格式高亮，Pandas可以直接可视化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化1: 缺失值热力图\n",
    "fig, axes = plt.subplots(1, 2, figsize=(16, 6))\n",
    "\n",
    "# 左图: 缺失值位置热力图\n",
    "sns.heatmap(df.isnull(), yticklabels=False, cbar=False, cmap='viridis', ax=axes[0])\n",
    "axes[0].set_title('Missing Values Heatmap (Yellow = Missing)', fontsize=14, fontweight='bold')\n",
    "axes[0].set_xlabel('Columns')\n",
    "\n",
    "# 右图: 各列缺失值统计柱状图\n",
    "missing_counts = df.isnull().sum()\n",
    "missing_counts = missing_counts[missing_counts > 0].sort_values(ascending=True)\n",
    "missing_counts.plot(kind='barh', ax=axes[1], color='coral', edgecolor='black')\n",
    "axes[1].set_title('Missing Values Count by Column', fontsize=14, fontweight='bold')\n",
    "axes[1].set_xlabel('Number of Missing Values')\n",
    "axes[1].grid(axis='x', alpha=0.3)\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化2: 缺失值矩阵（展示前30行）\n",
    "plt.figure(figsize=(12, 8))\n",
    "sns.heatmap(df.head(30).isnull(), cbar=True, cmap='coolwarm', \n",
    "            yticklabels=True, xticklabels=True)\n",
    "plt.title('Missing Values Matrix (First 30 Rows)', fontsize=14, fontweight='bold')\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.4 缺失值模式分析\n",
    "\n",
    "**重要**: 理解缺失模式有助于选择正确的处理策略"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 分析缺失值模式\n",
    "print(\"=== 缺失值模式分析 ===\")\n",
    "\n",
    "# 1. 薪资缺失与年龄、职位的关系\n",
    "print(\"\\n1. 薪资缺失情况分析:\")\n",
    "salary_missing = df['salary'].isnull()\n",
    "print(f\"有薪资数据的平均年龄: {df[~salary_missing]['age'].mean():.1f}\")\n",
    "print(f\"缺失薪资的平均年龄: {df[salary_missing]['age'].mean():.1f}\")\n",
    "\n",
    "print(\"\\n按职位统计薪资缺失:\")\n",
    "position_missing = df.groupby('position')['salary'].apply(lambda x: x.isnull().sum())\n",
    "print(position_missing)\n",
    "\n",
    "# 2. 绩效评分缺失与工作年限的关系\n",
    "print(\"\\n2. 绩效评分缺失与工作年限:\")\n",
    "perf_missing = df['performance_score'].isnull()\n",
    "print(f\"有绩效评分的平均工作年限: {df[~perf_missing]['years_of_service'].mean():.1f}年\")\n",
    "print(f\"缺失绩效评分的平均工作年限: {df[perf_missing]['years_of_service'].mean():.1f}年\")\n",
    "\n",
    "# 3. 相关性分析\n",
    "print(\"\\n3. 是否缺失salary与其他变量的关系:\")\n",
    "df['salary_missing'] = df['salary'].isnull().astype(int)\n",
    "correlations = df[['salary_missing', 'age', 'years_of_service']].corr()['salary_missing'].drop('salary_missing')\n",
    "print(correlations)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化缺失模式\n",
    "fig, axes = plt.subplots(1, 2, figsize=(14, 5))\n",
    "\n",
    "# 左图: 年龄分布 - 按薪资是否缺失分组\n",
    "df[~df['salary'].isnull()]['age'].hist(bins=15, alpha=0.5, label='Has Salary', ax=axes[0], color='blue')\n",
    "df[df['salary'].isnull()]['age'].hist(bins=15, alpha=0.5, label='Missing Salary', ax=axes[0], color='red')\n",
    "axes[0].set_title('Age Distribution by Salary Missing Status', fontweight='bold')\n",
    "axes[0].set_xlabel('Age')\n",
    "axes[0].set_ylabel('Frequency')\n",
    "axes[0].legend()\n",
    "axes[0].grid(alpha=0.3)\n",
    "\n",
    "# 右图: 工作年限分布 - 按绩效评分是否缺失分组\n",
    "df[~df['performance_score'].isnull()]['years_of_service'].hist(bins=10, alpha=0.5, label='Has Score', ax=axes[1], color='green')\n",
    "df[df['performance_score'].isnull()]['years_of_service'].hist(bins=10, alpha=0.5, label='Missing Score', ax=axes[1], color='orange')\n",
    "axes[1].set_title('Years of Service by Performance Score Missing', fontweight='bold')\n",
    "axes[1].set_xlabel('Years of Service')\n",
    "axes[1].set_ylabel('Frequency')\n",
    "axes[1].legend()\n",
    "axes[1].grid(alpha=0.3)\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 三、缺失值处理策略\n",
    "\n",
    "### 3.1 删除法\n",
    "\n",
    "**适用场景**:\n",
    "- 缺失比例很小（<5%）\n",
    "- 缺失是完全随机的\n",
    "- 数据量充足\n",
    "\n",
    "**优点**: 简单直接\n",
    "\n",
    "**缺点**: 损失数据，可能引入偏差\n",
    "\n",
    "**Excel对比**: Excel中需要手动筛选删除，Pandas一行代码搞定"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建副本用于演示\n",
    "df_drop = df.copy()\n",
    "\n",
    "print(\"=== 删除法示例 ===\")\n",
    "print(f\"原始数据: {df_drop.shape}\")\n",
    "\n",
    "# 方法1: 删除任何包含缺失值的行\n",
    "df_drop1 = df_drop.dropna()\n",
    "print(f\"\\n1. 删除任何含缺失的行: {df_drop1.shape} (删除了{len(df_drop)-len(df_drop1)}行)\")\n",
    "\n",
    "# 方法2: 删除全部为缺失值的行\n",
    "df_drop2 = df_drop.dropna(how='all')\n",
    "print(f\"2. 删除全部为缺失的行: {df_drop2.shape} (删除了{len(df_drop)-len(df_drop2)}行)\")\n",
    "\n",
    "# 方法3: 指定至少有N个非缺失值才保留\n",
    "df_drop3 = df_drop.dropna(thresh=10)  # 至少10个非缺失值\n",
    "print(f\"3. 至少10个非缺失值: {df_drop3.shape} (删除了{len(df_drop)-len(df_drop3)}行)\")\n",
    "\n",
    "# 方法4: 只考虑特定列\n",
    "df_drop4 = df_drop.dropna(subset=['employee_id', 'name', 'age'])\n",
    "print(f\"4. 删除ID/姓名/年龄缺失的行: {df_drop4.shape} (删除了{len(df_drop)-len(df_drop4)}行)\")\n",
    "\n",
    "# 方法5: 删除缺失值超过阈值的列\n",
    "threshold = 0.5  # 缺失超过50%的列\n",
    "df_drop5 = df_drop.dropna(axis=1, thresh=int(len(df_drop) * (1-threshold)))\n",
    "print(f\"5. 删除缺失>50%的列: {df_drop5.shape} (删除了{df_drop.shape[1]-df_drop5.shape[1]}列)\")\n",
    "print(f\"   删除的列: {set(df_drop.columns) - set(df_drop5.columns)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2 填充法 - 固定值填充"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 固定值填充\n",
    "df_fill = df.copy()\n",
    "\n",
    "print(\"=== 固定值填充 ===\")\n",
    "\n",
    "# 方法1: 填充为0\n",
    "df_fill['bonus'].fillna(0, inplace=True)\n",
    "print(f\"1. bonus列用0填充: {df_fill['bonus'].isnull().sum()}个缺失\")\n",
    "\n",
    "# 方法2: 填充为指定字符串\n",
    "df_fill['city'].fillna('未知', inplace=True)\n",
    "print(f\"2. city列用'未知'填充: {df_fill['city'].isnull().sum()}个缺失\")\n",
    "\n",
    "# 方法3: 填充为特定值（根据业务逻辑）\n",
    "df_fill['phone'].fillna('暂无联系方式', inplace=True)\n",
    "print(f\"3. phone列用'暂无联系方式'填充: {df_fill['phone'].isnull().sum()}个缺失\")\n",
    "\n",
    "print(\"\\n填充后示例:\")\n",
    "print(df_fill[['employee_id', 'phone', 'city', 'bonus']].head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3 填充法 - 统计值填充\n",
    "\n",
    "**最常用的方法！**\n",
    "\n",
    "**选择原则**:\n",
    "- **均值**: 数据近似正态分布，无极端异常值\n",
    "- **中位数**: 有异常值，或数据偏斜\n",
    "- **众数**: 分类变量，或离散数值变量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 统计值填充\n",
    "df_stat = df.copy()\n",
    "\n",
    "print(\"=== 统计值填充 ===\")\n",
    "\n",
    "# 1. 均值填充\n",
    "salary_mean = df_stat['salary'].mean()\n",
    "df_stat['salary_mean_filled'] = df_stat['salary'].fillna(salary_mean)\n",
    "print(f\"1. 薪资均值填充: 均值={salary_mean:.0f}\")\n",
    "print(f\"   缺失数: {df_stat['salary_mean_filled'].isnull().sum()}\")\n",
    "\n",
    "# 2. 中位数填充\n",
    "salary_median = df_stat['salary'].median()\n",
    "df_stat['salary_median_filled'] = df_stat['salary'].fillna(salary_median)\n",
    "print(f\"\\n2. 薪资中位数填充: 中位数={salary_median:.0f}\")\n",
    "print(f\"   缺失数: {df_stat['salary_median_filled'].isnull().sum()}\")\n",
    "\n",
    "# 3. 众数填充\n",
    "perf_mode = df_stat['performance_score'].mode()[0]\n",
    "df_stat['performance_score_filled'] = df_stat['performance_score'].fillna(perf_mode)\n",
    "print(f\"\\n3. 绩效评分众数填充: 众数={perf_mode}\")\n",
    "print(f\"   缺失数: {df_stat['performance_score_filled'].isnull().sum()}\")\n",
    "\n",
    "# 对比不同填充方法\n",
    "print(\"\\n=== 填充方法对比 ===\")\n",
    "comparison = pd.DataFrame({\n",
    "    '原始数据': [df_stat['salary'].mean(), df_stat['salary'].median(), df_stat['salary'].std()],\n",
    "    '均值填充': [df_stat['salary_mean_filled'].mean(), df_stat['salary_mean_filled'].median(), df_stat['salary_mean_filled'].std()],\n",
    "    '中位数填充': [df_stat['salary_median_filled'].mean(), df_stat['salary_median_filled'].median(), df_stat['salary_median_filled'].std()]\n",
    "}, index=['均值', '中位数', '标准差'])\n",
    "print(comparison.round(2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化不同填充方法的效果\n",
    "fig, axes = plt.subplots(1, 3, figsize=(18, 5))\n",
    "\n",
    "# 原始数据分布\n",
    "df_stat['salary'].dropna().hist(bins=20, ax=axes[0], edgecolor='black', alpha=0.7, color='skyblue')\n",
    "axes[0].axvline(df_stat['salary'].mean(), color='red', linestyle='--', linewidth=2, label=f'Mean: {salary_mean:.0f}')\n",
    "axes[0].axvline(df_stat['salary'].median(), color='green', linestyle='--', linewidth=2, label=f'Median: {salary_median:.0f}')\n",
    "axes[0].set_title('Original Salary Distribution', fontsize=12, fontweight='bold')\n",
    "axes[0].set_xlabel('Salary')\n",
    "axes[0].legend()\n",
    "axes[0].grid(alpha=0.3)\n",
    "\n",
    "# 均值填充后\n",
    "df_stat['salary_mean_filled'].hist(bins=20, ax=axes[1], edgecolor='black', alpha=0.7, color='lightcoral')\n",
    "axes[1].axvline(salary_mean, color='red', linestyle='--', linewidth=2, label='Filled Value')\n",
    "axes[1].set_title('After Mean Imputation', fontsize=12, fontweight='bold')\n",
    "axes[1].set_xlabel('Salary')\n",
    "axes[1].legend()\n",
    "axes[1].grid(alpha=0.3)\n",
    "\n",
    "# 中位数填充后\n",
    "df_stat['salary_median_filled'].hist(bins=20, ax=axes[2], edgecolor='black', alpha=0.7, color='lightgreen')\n",
    "axes[2].axvline(salary_median, color='green', linestyle='--', linewidth=2, label='Filled Value')\n",
    "axes[2].set_title('After Median Imputation', fontsize=12, fontweight='bold')\n",
    "axes[2].set_xlabel('Salary')\n",
    "axes[2].legend()\n",
    "axes[2].grid(alpha=0.3)\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4 填充法 - 前向/后向填充\n",
    "\n",
    "**适用场景**: 时间序列数据，相邻值相似\n",
    "\n",
    "**方法**:\n",
    "- `ffill()` / `pad()`: 前向填充（用前一个值）\n",
    "- `bfill()` / `backfill()`: 后向填充（用后一个值）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建时间序列示例\n",
    "time_series = pd.DataFrame({\n",
    "    'date': pd.date_range('2024-01-01', periods=10, freq='D'),\n",
    "    'temperature': [15, 16, np.nan, np.nan, 19, 20, np.nan, 22, 23, np.nan]\n",
    "})\n",
    "\n",
    "print(\"=== 前向/后向填充 ===\")\n",
    "print(\"\\n原始数据:\")\n",
    "print(time_series)\n",
    "\n",
    "# 前向填充\n",
    "time_series['temp_ffill'] = time_series['temperature'].fillna(method='ffill')\n",
    "print(\"\\n前向填充（用前一天的温度）:\")\n",
    "print(time_series)\n",
    "\n",
    "# 后向填充\n",
    "time_series['temp_bfill'] = time_series['temperature'].fillna(method='bfill')\n",
    "print(\"\\n后向填充（用后一天的温度）:\")\n",
    "print(time_series[['date', 'temperature', 'temp_bfill']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.5 填充法 - 插值法\n",
    "\n",
    "**适用场景**: 数值型连续变量，需要更平滑的填充\n",
    "\n",
    "**方法**:\n",
    "- `linear`: 线性插值（默认）\n",
    "- `polynomial`: 多项式插值\n",
    "- `spline`: 样条插值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 插值法示例\n",
    "print(\"=== 插值法 ===\")\n",
    "\n",
    "# 线性插值\n",
    "time_series['temp_linear'] = time_series['temperature'].interpolate(method='linear')\n",
    "\n",
    "# 多项式插值\n",
    "time_series['temp_poly'] = time_series['temperature'].interpolate(method='polynomial', order=2)\n",
    "\n",
    "print(\"\\n不同插值方法对比:\")\n",
    "print(time_series[['date', 'temperature', 'temp_ffill', 'temp_linear', 'temp_poly']].round(2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化插值效果\n",
    "plt.figure(figsize=(12, 6))\n",
    "plt.plot(time_series['date'], time_series['temperature'], 'ko-', label='Original', markersize=8, linewidth=2)\n",
    "plt.plot(time_series['date'], time_series['temp_ffill'], 's--', label='Forward Fill', alpha=0.7, markersize=6)\n",
    "plt.plot(time_series['date'], time_series['temp_linear'], '^--', label='Linear Interpolation', alpha=0.7, markersize=6)\n",
    "plt.plot(time_series['date'], time_series['temp_poly'], 'd--', label='Polynomial Interpolation', alpha=0.7, markersize=6)\n",
    "plt.title('Comparison of Different Imputation Methods', fontsize=14, fontweight='bold')\n",
    "plt.xlabel('Date')\n",
    "plt.ylabel('Temperature')\n",
    "plt.legend()\n",
    "plt.grid(alpha=0.3)\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.6 分组填充\n",
    "\n",
    "**策略**: 按类别分组，用各组的统计值填充\n",
    "\n",
    "**优势**: 更符合实际情况，避免统一填充带来的偏差"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 分组填充示例\n",
    "df_group = df.copy()\n",
    "\n",
    "print(\"=== 分组填充 ===\")\n",
    "\n",
    "# 按部门填充薪资（用各部门的平均薪资）\n",
    "df_group['salary_by_dept'] = df_group.groupby('department')['salary'].transform(\n",
    "    lambda x: x.fillna(x.mean())\n",
    ")\n",
    "\n",
    "print(\"\\n按部门填充薪资 - 各部门均值:\")\n",
    "dept_salary = df_group.groupby('department')['salary'].mean()\n",
    "print(dept_salary.round(0))\n",
    "\n",
    "print(\"\\n填充示例（缺失的用部门均值）:\")\n",
    "sample = df_group[['employee_id', 'department', 'salary', 'salary_by_dept']].head(20)\n",
    "print(sample)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 对比全局填充vs分组填充\n",
    "df_group['salary_global'] = df_group['salary'].fillna(df_group['salary'].mean())\n",
    "\n",
    "print(\"\\n=== 全局填充 vs 分组填充对比 ===\")\n",
    "comparison = df_group.groupby('department').agg({\n",
    "    'salary': 'mean',\n",
    "    'salary_global': 'mean',\n",
    "    'salary_by_dept': 'mean'\n",
    "}).round(0)\n",
    "comparison.columns = ['原始均值', '全局填充后', '分组填充后']\n",
    "print(comparison)\n",
    "\n",
    "print(\"\\n结论: 分组填充保持了各组的特征，全局填充会拉平差异\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.7 缺失值处理决策树\n",
    "\n",
    "```\n",
    "开始\n",
    "  ↓\n",
    "缺失比例 > 50%?\n",
    "  ├─ 是 → 考虑删除该列\n",
    "  └─ 否 ↓\n",
    "       数据量充足 且 缺失<5%?\n",
    "         ├─ 是 → 删除缺失行\n",
    "         └─ 否 ↓\n",
    "              是否分类变量?\n",
    "                ├─ 是 → 众数填充 或 \"缺失\"类别\n",
    "                └─ 否 ↓\n",
    "                     是否时间序列?\n",
    "                       ├─ 是 → 插值法 或 前向填充\n",
    "                       └─ 否 ↓\n",
    "                            有极端值?\n",
    "                              ├─ 是 → 中位数填充\n",
    "                              └─ 否 → 均值填充\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 四、异常值检测\n",
    "\n",
    "### 4.1 什么是异常值？\n",
    "\n",
    "**定义**: 与数据集中大多数观测值显著不同的值\n",
    "\n",
    "**类型**:\n",
    "1. **点异常**: 单个数据点异常（如年龄-5岁）\n",
    "2. **上下文异常**: 在特定上下文下异常（如夏天温度-10°C）\n",
    "3. **集体异常**: 一组数据整体异常\n",
    "\n",
    "**产生原因**:\n",
    "- 数据录入错误\n",
    "- 测量误差\n",
    "- 真实的极端值\n",
    "- 数据处理错误"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建包含异常值的数据集\n",
    "np.random.seed(42)\n",
    "n = 100\n",
    "\n",
    "# 正常数据\n",
    "normal_salary = np.random.normal(20000, 5000, 95)\n",
    "\n",
    "# 异常值\n",
    "outliers = np.array([-5000, 150000, 200000, 250000, 999999])\n",
    "\n",
    "# 合并\n",
    "all_salary = np.concatenate([normal_salary, outliers])\n",
    "\n",
    "df_outlier = pd.DataFrame({\n",
    "    'employee_id': [f'E{str(i).zfill(4)}' for i in range(1, n+1)],\n",
    "    'salary': all_salary,\n",
    "    'age': np.random.randint(22, 60, n),\n",
    "    'experience_years': np.random.randint(0, 30, n)\n",
    "})\n",
    "\n",
    "# 添加一些年龄异常值\n",
    "df_outlier.loc[[5, 15, 25], 'age'] = [-5, 150, 200]\n",
    "\n",
    "print(\"✅ 创建了包含异常值的数据集\")\n",
    "print(f\"数据规模: {df_outlier.shape}\")\n",
    "print(\"\\n数据样本:\")\n",
    "print(df_outlier.head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2 方法1: 业务规则法\n",
    "\n",
    "**最直接有效的方法！**\n",
    "\n",
    "**原理**: 基于业务知识设定合理范围"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 业务规则检测\n",
    "print(\"=== 业务规则法检测异常值 ===\")\n",
    "\n",
    "# 规则1: 年龄应在18-65之间\n",
    "age_outliers = df_outlier[(df_outlier['age'] < 18) | (df_outlier['age'] > 65)]\n",
    "print(f\"\\n1. 年龄异常（<18 或 >65）: {len(age_outliers)}个\")\n",
    "print(age_outliers[['employee_id', 'age']])\n",
    "\n",
    "# 规则2: 薪资应在3000-100000之间\n",
    "salary_outliers = df_outlier[(df_outlier['salary'] < 3000) | (df_outlier['salary'] > 100000)]\n",
    "print(f\"\\n2. 薪资异常（<3000 或 >100000）: {len(salary_outliers)}个\")\n",
    "print(salary_outliers[['employee_id', 'salary']].sort_values('salary'))\n",
    "\n",
    "# 规则3: 工作年限不能超过年龄-18\n",
    "exp_outliers = df_outlier[df_outlier['experience_years'] > (df_outlier['age'] - 18)]\n",
    "print(f\"\\n3. 工作年限异常（超过年龄-18）: {len(exp_outliers)}个\")\n",
    "print(exp_outliers[['employee_id', 'age', 'experience_years']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.3 方法2: 统计法 - 3σ原则\n",
    "\n",
    "**原理**: 假设数据服从正态分布\n",
    "- 99.7%的数据在 μ±3σ 范围内\n",
    "- 超出此范围的视为异常\n",
    "\n",
    "**公式**:\n",
    "- 下界: mean - 3 × std\n",
    "- 上界: mean + 3 × std\n",
    "\n",
    "**适用**: 数据近似正态分布"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3σ原则检测\n",
    "def detect_outliers_3sigma(data, column):\n",
    "    \"\"\"\n",
    "    使用3σ原则检测异常值\n",
    "    \"\"\"\n",
    "    mean = data[column].mean()\n",
    "    std = data[column].std()\n",
    "    lower_bound = mean - 3 * std\n",
    "    upper_bound = mean + 3 * std\n",
    "    \n",
    "    outliers = data[(data[column] < lower_bound) | (data[column] > upper_bound)]\n",
    "    \n",
    "    print(f\"\\n=== {column} 的3σ检测 ===\")\n",
    "    print(f\"均值: {mean:.2f}\")\n",
    "    print(f\"标准差: {std:.2f}\")\n",
    "    print(f\"正常范围: [{lower_bound:.2f}, {upper_bound:.2f}]\")\n",
    "    print(f\"异常值数量: {len(outliers)}\")\n",
    "    \n",
    "    return outliers, lower_bound, upper_bound\n",
    "\n",
    "# 检测薪资异常\n",
    "salary_outliers_3sigma, lower, upper = detect_outliers_3sigma(df_outlier, 'salary')\n",
    "print(\"\\n异常值:\")\n",
    "print(salary_outliers_3sigma[['employee_id', 'salary']].sort_values('salary'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.4 方法3: IQR法（四分位距法）⭐推荐\n",
    "\n",
    "**原理**: 基于数据的分位数，不假设分布\n",
    "\n",
    "**步骤**:\n",
    "1. 计算Q1（25%分位数）和Q3（75%分位数）\n",
    "2. 计算IQR = Q3 - Q1\n",
    "3. 下界 = Q1 - 1.5 × IQR\n",
    "4. 上界 = Q3 + 1.5 × IQR\n",
    "5. 超出界限的为异常值\n",
    "\n",
    "**优点**: \n",
    "- 不受极端值影响\n",
    "- 不假设数据分布\n",
    "- 箱线图的理论基础\n",
    "\n",
    "**Excel对比**: Excel可以用箱线图，但需要手动计算边界，Pandas自动化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# IQR法检测\n",
    "def detect_outliers_iqr(data, column):\n",
    "    \"\"\"\n",
    "    使用IQR方法检测异常值\n",
    "    \"\"\"\n",
    "    Q1 = data[column].quantile(0.25)\n",
    "    Q3 = data[column].quantile(0.75)\n",
    "    IQR = Q3 - Q1\n",
    "    \n",
    "    lower_bound = Q1 - 1.5 * IQR\n",
    "    upper_bound = Q3 + 1.5 * IQR\n",
    "    \n",
    "    outliers = data[(data[column] < lower_bound) | (data[column] > upper_bound)]\n",
    "    \n",
    "    print(f\"\\n=== {column} 的IQR检测 ===\")\n",
    "    print(f\"Q1 (25%分位数): {Q1:.2f}\")\n",
    "    print(f\"Q3 (75%分位数): {Q3:.2f}\")\n",
    "    print(f\"IQR: {IQR:.2f}\")\n",
    "    print(f\"正常范围: [{lower_bound:.2f}, {upper_bound:.2f}]\")\n",
    "    print(f\"异常值数量: {len(outliers)} ({len(outliers)/len(data)*100:.1f}%)\")\n",
    "    \n",
    "    return outliers, lower_bound, upper_bound\n",
    "\n",
    "# 检测薪资异常\n",
    "salary_outliers_iqr, iqr_lower, iqr_upper = detect_outliers_iqr(df_outlier, 'salary')\n",
    "print(\"\\n异常值详情:\")\n",
    "print(salary_outliers_iqr[['employee_id', 'salary']].sort_values('salary'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.5 方法4: Z-Score法\n",
    "\n",
    "**原理**: 标准化后的值，表示距离均值多少个标准差\n",
    "\n",
    "**公式**: Z = (X - μ) / σ\n",
    "\n",
    "**判断**: |Z| > 3 为异常值（也可以用2.5或3.5）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Z-Score法检测\n",
    "def detect_outliers_zscore(data, column, threshold=3):\n",
    "    \"\"\"\n",
    "    使用Z-Score方法检测异常值\n",
    "    \"\"\"\n",
    "    mean = data[column].mean()\n",
    "    std = data[column].std()\n",
    "    \n",
    "    # 计算Z-Score\n",
    "    data['z_score'] = np.abs((data[column] - mean) / std)\n",
    "    \n",
    "    outliers = data[data['z_score'] > threshold]\n",
    "    \n",
    "    print(f\"\\n=== {column} 的Z-Score检测 ===\")\n",
    "    print(f\"阈值: |Z| > {threshold}\")\n",
    "    print(f\"异常值数量: {len(outliers)}\")\n",
    "    \n",
    "    return outliers\n",
    "\n",
    "# 检测薪资异常\n",
    "salary_outliers_z = detect_outliers_zscore(df_outlier.copy(), 'salary')\n",
    "print(\"\\n异常值详情:\")\n",
    "print(salary_outliers_z[['employee_id', 'salary', 'z_score']].sort_values('z_score', ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.6 异常值可视化\n",
    "\n",
    "**箱线图（Boxplot）**: 最直观的异常值可视化方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 综合可视化异常值检测结果\n",
    "fig, axes = plt.subplots(2, 2, figsize=(16, 12))\n",
    "\n",
    "# 1. 箱线图\n",
    "axes[0, 0].boxplot(df_outlier['salary'], vert=True)\n",
    "axes[0, 0].set_title('Salary Boxplot (IQR Method)', fontsize=14, fontweight='bold')\n",
    "axes[0, 0].set_ylabel('Salary')\n",
    "axes[0, 0].axhline(y=iqr_lower, color='r', linestyle='--', label=f'Lower Bound: {iqr_lower:.0f}')\n",
    "axes[0, 0].axhline(y=iqr_upper, color='r', linestyle='--', label=f'Upper Bound: {iqr_upper:.0f}')\n",
    "axes[0, 0].legend()\n",
    "axes[0, 0].grid(alpha=0.3)\n",
    "\n",
    "# 2. 直方图\n",
    "axes[0, 1].hist(df_outlier['salary'], bins=30, edgecolor='black', alpha=0.7, color='skyblue')\n",
    "axes[0, 1].axvline(x=iqr_lower, color='r', linestyle='--', linewidth=2, label='IQR Bounds')\n",
    "axes[0, 1].axvline(x=iqr_upper, color='r', linestyle='--', linewidth=2)\n",
    "axes[0, 1].set_title('Salary Distribution with IQR Bounds', fontsize=14, fontweight='bold')\n",
    "axes[0, 1].set_xlabel('Salary')\n",
    "axes[0, 1].set_ylabel('Frequency')\n",
    "axes[0, 1].legend()\n",
    "axes[0, 1].grid(alpha=0.3)\n",
    "\n",
    "# 3. 散点图 - 按索引显示\n",
    "axes[1, 0].scatter(df_outlier.index, df_outlier['salary'], alpha=0.6, s=50)\n",
    "axes[1, 0].axhline(y=iqr_lower, color='r', linestyle='--', linewidth=2, label='IQR Bounds')\n",
    "axes[1, 0].axhline(y=iqr_upper, color='r', linestyle='--', linewidth=2)\n",
    "# 标注异常值\n",
    "outlier_indices = salary_outliers_iqr.index\n",
    "axes[1, 0].scatter(outlier_indices, df_outlier.loc[outlier_indices, 'salary'], \n",
    "                   color='red', s=100, marker='x', linewidths=3, label='Outliers')\n",
    "axes[1, 0].set_title('Salary Scatter Plot with Outliers', fontsize=14, fontweight='bold')\n",
    "axes[1, 0].set_xlabel('Index')\n",
    "axes[1, 0].set_ylabel('Salary')\n",
    "axes[1, 0].legend()\n",
    "axes[1, 0].grid(alpha=0.3)\n",
    "\n",
    "# 4. 3种方法对比\n",
    "methods_comparison = pd.DataFrame({\n",
    "    '方法': ['业务规则', '3σ原则', 'IQR法', 'Z-Score'],\n",
    "    '异常值数量': [\n",
    "        len(salary_outliers),\n",
    "        len(salary_outliers_3sigma),\n",
    "        len(salary_outliers_iqr),\n",
    "        len(salary_outliers_z)\n",
    "    ]\n",
    "})\n",
    "axes[1, 1].bar(methods_comparison['方法'], methods_comparison['异常值数量'], \n",
    "               color=['#ff9999', '#66b3ff', '#99ff99', '#ffcc99'], edgecolor='black')\n",
    "axes[1, 1].set_title('Outlier Detection Methods Comparison', fontsize=14, fontweight='bold')\n",
    "axes[1, 1].set_ylabel('Number of Outliers')\n",
    "axes[1, 1].grid(axis='y', alpha=0.3)\n",
    "for i, v in enumerate(methods_comparison['异常值数量']):\n",
    "    axes[1, 1].text(i, v + 0.1, str(v), ha='center', va='bottom', fontweight='bold')\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 五、异常值处理策略\n",
    "\n",
    "### 5.1 策略1: 删除\n",
    "\n",
    "**适用**: 确认是错误数据，且数量不多"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 删除异常值\n",
    "df_remove = df_outlier.copy()\n",
    "\n",
    "print(\"=== 删除异常值 ===\")\n",
    "print(f\"原始数据: {len(df_remove)}行\")\n",
    "\n",
    "# 使用IQR法识别的异常值\n",
    "Q1 = df_remove['salary'].quantile(0.25)\n",
    "Q3 = df_remove['salary'].quantile(0.75)\n",
    "IQR = Q3 - Q1\n",
    "lower = Q1 - 1.5 * IQR\n",
    "upper = Q3 + 1.5 * IQR\n",
    "\n",
    "# 删除异常值\n",
    "df_remove = df_remove[(df_remove['salary'] >= lower) & (df_remove['salary'] <= upper)]\n",
    "print(f\"删除异常值后: {len(df_remove)}行\")\n",
    "print(f\"删除了: {len(df_outlier) - len(df_remove)}行 ({(len(df_outlier)-len(df_remove))/len(df_outlier)*100:.1f}%)\")\n",
    "\n",
    "print(\"\\n删除后的统计:\")\n",
    "print(df_remove['salary'].describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.2 策略2: 替换为边界值（Capping/Flooring）\n",
    "\n",
    "**适用**: 想保留数据量，但限制极端值影响"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 替换为边界值\n",
    "df_cap = df_outlier.copy()\n",
    "\n",
    "print(\"=== 替换为边界值 ===\")\n",
    "print(f\"\\n替换前 - 薪资范围: [{df_cap['salary'].min():.0f}, {df_cap['salary'].max():.0f}]\")\n",
    "\n",
    "# 方法1: 使用IQR边界\n",
    "df_cap['salary_capped'] = df_cap['salary'].clip(lower=lower, upper=upper)\n",
    "\n",
    "print(f\"替换后 - 薪资范围: [{df_cap['salary_capped'].min():.0f}, {df_cap['salary_capped'].max():.0f}]\")\n",
    "print(f\"\\n被替换的异常值数量: {(df_cap['salary'] != df_cap['salary_capped']).sum()}\")\n",
    "\n",
    "# 查看被替换的数据\n",
    "print(\"\\n被替换的记录:\")\n",
    "changed = df_cap[df_cap['salary'] != df_cap['salary_capped']][['employee_id', 'salary', 'salary_capped']]\n",
    "print(changed.sort_values('salary'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.3 策略3: 替换为统计值\n",
    "\n",
    "**适用**: 异常值较少，用中位数或均值替换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 替换为统计值\n",
    "df_replace = df_outlier.copy()\n",
    "\n",
    "print(\"=== 替换为统计值 ===\")\n",
    "\n",
    "# 识别异常值\n",
    "is_outlier = (df_replace['salary'] < lower) | (df_replace['salary'] > upper)\n",
    "\n",
    "# 计算非异常值的中位数\n",
    "median_salary = df_replace[~is_outlier]['salary'].median()\n",
    "\n",
    "# 替换异常值为中位数\n",
    "df_replace['salary_replaced'] = df_replace['salary'].copy()\n",
    "df_replace.loc[is_outlier, 'salary_replaced'] = median_salary\n",
    "\n",
    "print(f\"中位数: {median_salary:.0f}\")\n",
    "print(f\"替换了 {is_outlier.sum()} 个异常值\")\n",
    "\n",
    "print(\"\\n替换前后对比:\")\n",
    "comparison = pd.DataFrame({\n",
    "    '指标': ['均值', '中位数', '标准差', '最小值', '最大值'],\n",
    "    '原始数据': [\n",
    "        df_replace['salary'].mean(),\n",
    "        df_replace['salary'].median(),\n",
    "        df_replace['salary'].std(),\n",
    "        df_replace['salary'].min(),\n",
    "        df_replace['salary'].max()\n",
    "    ],\n",
    "    '替换后': [\n",
    "        df_replace['salary_replaced'].mean(),\n",
    "        df_replace['salary_replaced'].median(),\n",
    "        df_replace['salary_replaced'].std(),\n",
    "        df_replace['salary_replaced'].min(),\n",
    "        df_replace['salary_replaced'].max()\n",
    "    ]\n",
    "})\n",
    "print(comparison.round(2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.4 策略4: 保留并标记\n",
    "\n",
    "**适用**: 不确定是否应该删除，保留但做标记"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保留并标记\n",
    "df_flag = df_outlier.copy()\n",
    "\n",
    "print(\"=== 保留并标记异常值 ===\")\n",
    "\n",
    "# 添加标记列\n",
    "df_flag['is_outlier'] = ((df_flag['salary'] < lower) | (df_flag['salary'] > upper))\n",
    "df_flag['outlier_type'] = 'normal'\n",
    "df_flag.loc[df_flag['salary'] < lower, 'outlier_type'] = 'too_low'\n",
    "df_flag.loc[df_flag['salary'] > upper, 'outlier_type'] = 'too_high'\n",
    "\n",
    "print(\"\\n异常值统计:\")\n",
    "print(df_flag['outlier_type'].value_counts())\n",
    "\n",
    "print(\"\\n标记为异常的记录:\")\n",
    "print(df_flag[df_flag['is_outlier']][['employee_id', 'salary', 'outlier_type']].sort_values('salary'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.5 不同策略效果对比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 可视化不同处理策略的效果\n",
    "fig, axes = plt.subplots(2, 3, figsize=(18, 10))\n",
    "\n",
    "# 1. 原始数据\n",
    "axes[0, 0].hist(df_outlier['salary'], bins=30, edgecolor='black', alpha=0.7, color='lightblue')\n",
    "axes[0, 0].set_title('Original Data', fontsize=12, fontweight='bold')\n",
    "axes[0, 0].set_xlabel('Salary')\n",
    "axes[0, 0].set_ylabel('Frequency')\n",
    "axes[0, 0].grid(alpha=0.3)\n",
    "\n",
    "# 2. 删除异常值后\n",
    "axes[0, 1].hist(df_remove['salary'], bins=30, edgecolor='black', alpha=0.7, color='lightcoral')\n",
    "axes[0, 1].set_title('After Removal', fontsize=12, fontweight='bold')\n",
    "axes[0, 1].set_xlabel('Salary')\n",
    "axes[0, 1].grid(alpha=0.3)\n",
    "\n",
    "# 3. 替换为边界值后\n",
    "axes[0, 2].hist(df_cap['salary_capped'], bins=30, edgecolor='black', alpha=0.7, color='lightgreen')\n",
    "axes[0, 2].set_title('After Capping', fontsize=12, fontweight='bold')\n",
    "axes[0, 2].set_xlabel('Salary')\n",
    "axes[0, 2].grid(alpha=0.3)\n",
    "\n",
    "# 4. 替换为中位数后\n",
    "axes[1, 0].hist(df_replace['salary_replaced'], bins=30, edgecolor='black', alpha=0.7, color='lightyellow')\n",
    "axes[1, 0].set_title('After Median Replacement', fontsize=12, fontweight='bold')\n",
    "axes[1, 0].set_xlabel('Salary')\n",
    "axes[1, 0].set_ylabel('Frequency')\n",
    "axes[1, 0].grid(alpha=0.3)\n",
    "\n",
    "# 5. 箱线图对比\n",
    "data_to_plot = [\n",
    "    df_outlier['salary'],\n",
    "    df_remove['salary'],\n",
    "    df_cap['salary_capped'],\n",
    "    df_replace['salary_replaced']\n",
    "]\n",
    "axes[1, 1].boxplot(data_to_plot, labels=['Original', 'Removed', 'Capped', 'Replaced'])\n",
    "axes[1, 1].set_title('Boxplot Comparison', fontsize=12, fontweight='bold')\n",
    "axes[1, 1].set_ylabel('Salary')\n",
    "axes[1, 1].grid(alpha=0.3)\n",
    "\n",
    "# 6. 统计对比表\n",
    "stats_comparison = pd.DataFrame({\n",
    "    'Original': df_outlier['salary'].describe(),\n",
    "    'Removed': df_remove['salary'].describe(),\n",
    "    'Capped': df_cap['salary_capped'].describe(),\n",
    "    'Replaced': df_replace['salary_replaced'].describe()\n",
    "}).T[['mean', 'std', 'min', 'max']].round(0)\n",
    "\n",
    "axes[1, 2].axis('tight')\n",
    "axes[1, 2].axis('off')\n",
    "table = axes[1, 2].table(cellText=stats_comparison.values,\n",
    "                        rowLabels=stats_comparison.index,\n",
    "                        colLabels=stats_comparison.columns,\n",
    "                        cellLoc='center',\n",
    "                        loc='center')\n",
    "table.auto_set_font_size(False)\n",
    "table.set_fontsize(10)\n",
    "table.scale(1, 2)\n",
    "axes[1, 2].set_title('Statistics Comparison', fontsize=12, fontweight='bold', pad=20)\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 六、完整清洗实战案例\n",
    "\n",
    "### 案例：清洗真实的员工数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建模拟真实场景的脏数据\n",
    "np.random.seed(42)\n",
    "n = 100\n",
    "\n",
    "messy_data = {\n",
    "    'employee_id': [f'E{str(i).zfill(4)}' if i % 10 != 0 else None for i in range(1, n+1)],\n",
    "    'name': [f'员工{i}' if i % 15 != 0 else None for i in range(1, n+1)],\n",
    "    'age': np.concatenate([np.random.randint(22, 60, 95), [-5, 150, 200, 0, 999]]),\n",
    "    'department': np.random.choice(['销售部', '技术部', '市场部', '人力资源', None], n, p=[0.3, 0.3, 0.2, 0.15, 0.05]),\n",
    "    'salary': np.concatenate([np.random.randint(5000, 50000, 90), [-1000, 0, 200000, 500000, 999999] + [None]*5]),\n",
    "    'hire_date': pd.date_range('2020-01-01', periods=n, freq='3D'),\n",
    "    'email': [f'emp{i}@company.com' if i % 20 != 0 else None for i in range(1, n+1)],\n",
    "}\n",
    "\n",
    "df_messy = pd.DataFrame(messy_data)\n",
    "\n",
    "# 添加重复行\n",
    "df_messy = pd.concat([df_messy, df_messy.iloc[[10, 11, 12]]], ignore_index=True)\n",
    "\n",
    "print(\"✅ 创建了真实场景的脏数据\")\n",
    "print(f\"数据规模: {df_messy.shape}\")\n",
    "print(\"\\n数据预览:\")\n",
    "print(df_messy.head(15))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 步骤1: 数据质量检查\n",
    "print(\"=\"*70)\n",
    "print(\"步骤1: 数据质量检查\")\n",
    "print(\"=\"*70)\n",
    "\n",
    "print(\"\\n1.1 缺失值检查:\")\n",
    "print(df_messy.isnull().sum())\n",
    "\n",
    "print(\"\\n1.2 重复值检查:\")\n",
    "print(f\"重复行数: {df_messy.duplicated().sum()}\")\n",
    "\n",
    "print(\"\\n1.3 数据类型:\")\n",
    "print(df_messy.dtypes)\n",
    "\n",
    "print(\"\\n1.4 描述性统计:\")\n",
    "print(df_messy[['age', 'salary']].describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 步骤2: 开始清洗\n",
    "df_clean = df_messy.copy()\n",
    "\n",
    "print(\"=\"*70)\n",
    "print(\"步骤2: 数据清洗\")\n",
    "print(\"=\"*70)\n",
    "\n",
    "print(f\"\\n清洗前: {df_clean.shape}\")\n",
    "\n",
    "# 2.1 删除完全重复的行\n",
    "df_clean = df_clean.drop_duplicates()\n",
    "print(f\"2.1 去重后: {df_clean.shape}\")\n",
    "\n",
    "# 2.2 删除employee_id缺失的行（关键字段）\n",
    "df_clean = df_clean.dropna(subset=['employee_id'])\n",
    "print(f\"2.2 删除ID缺失后: {df_clean.shape}\")\n",
    "\n",
    "# 2.3 处理年龄异常值\n",
    "# 业务规则: 年龄应在18-65之间\n",
    "print(f\"\\n2.3 年龄异常值: {((df_clean['age'] < 18) | (df_clean['age'] > 65)).sum()}个\")\n",
    "df_clean = df_clean[(df_clean['age'] >= 18) & (df_clean['age'] <= 65)]\n",
    "print(f\"     删除后: {df_clean.shape}\")\n",
    "\n",
    "# 2.4 处理薪资异常值\n",
    "print(f\"\\n2.4 薪资统计:\")\n",
    "print(f\"     最小值: {df_clean['salary'].min():.0f}\")\n",
    "print(f\"     最大值: {df_clean['salary'].max():.0f}\")\n",
    "\n",
    "# 业务规则: 薪资应在3000-100000之间\n",
    "salary_outliers = ((df_clean['salary'] < 3000) | (df_clean['salary'] > 100000)).sum()\n",
    "print(f\"     异常值数量: {salary_outliers}\")\n",
    "df_clean = df_clean[(df_clean['salary'] >= 3000) & (df_clean['salary'] <= 100000)]\n",
    "print(f\"     删除后: {df_clean.shape}\")\n",
    "\n",
    "# 2.5 填充缺失值\n",
    "print(\"\\n2.5 填充缺失值:\")\n",
    "# 姓名缺失用'未知姓名'\n",
    "df_clean['name'].fillna('未知姓名', inplace=True)\n",
    "print(f\"     name缺失: {df_clean['name'].isnull().sum()}\")\n",
    "\n",
    "# 部门缺失用众数\n",
    "dept_mode = df_clean['department'].mode()[0]\n",
    "df_clean['department'].fillna(dept_mode, inplace=True)\n",
    "print(f\"     department缺失: {df_clean['department'].isnull().sum()}\")\n",
    "\n",
    "# 薪资缺失用部门中位数\n",
    "df_clean['salary'] = df_clean.groupby('department')['salary'].transform(\n",
    "    lambda x: x.fillna(x.median())\n",
    ")\n",
    "print(f\"     salary缺失: {df_clean['salary'].isnull().sum()}\")\n",
    "\n",
    "# 邮箱缺失保持\n",
    "print(f\"     email缺失: {df_clean['email'].isnull().sum()} (保留)\")\n",
    "\n",
    "print(f\"\\n✅ 清洗完成: {df_clean.shape}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 步骤3: 清洗前后对比\n",
    "print(\"=\"*70)\n",
    "print(\"步骤3: 清洗前后对比\")\n",
    "print(\"=\"*70)\n",
    "\n",
    "comparison = pd.DataFrame({\n",
    "    '指标': [\n",
    "        '总行数',\n",
    "        '总列数',\n",
    "        '缺失值总数',\n",
    "        '重复行数',\n",
    "        '平均年龄',\n",
    "        '平均薪资',\n",
    "        '薪资标准差'\n",
    "    ],\n",
    "    '清洗前': [\n",
    "        len(df_messy),\n",
    "        len(df_messy.columns),\n",
    "        df_messy.isnull().sum().sum(),\n",
    "        df_messy.duplicated().sum(),\n",
    "        df_messy['age'].mean(),\n",
    "        df_messy['salary'].mean(),\n",
    "        df_messy['salary'].std()\n",
    "    ],\n",
    "    '清洗后': [\n",
    "        len(df_clean),\n",
    "        len(df_clean.columns),\n",
    "        df_clean.isnull().sum().sum(),\n",
    "        df_clean.duplicated().sum(),\n",
    "        df_clean['age'].mean(),\n",
    "        df_clean['salary'].mean(),\n",
    "        df_clean['salary'].std()\n",
    "    ]\n",
    "})\n",
    "\n",
    "print(comparison.round(2))\n",
    "\n",
    "print(\"\\n✅ 数据清洗总结:\")\n",
    "print(f\"   删除了 {len(df_messy)-len(df_clean)} 行脏数据 ({(len(df_messy)-len(df_clean))/len(df_messy)*100:.1f}%)\")\n",
    "print(f\"   缺失值从 {df_messy.isnull().sum().sum()} 减少到 {df_clean.isnull().sum().sum()}\")\n",
    "print(f\"   数据质量显著提升！\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导出清洗后的数据\n",
    "df_clean.to_csv('cleaned_employee_data.csv', index=False, encoding='utf-8-sig')\n",
    "df_clean.to_excel('cleaned_employee_data.xlsx', index=False)\n",
    "print(\"\\n✅ 清洗后的数据已导出:\")\n",
    "print(\"   - cleaned_employee_data.csv\")\n",
    "print(\"   - cleaned_employee_data.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 七、本讲总结\n",
    "\n",
    "### 核心知识点\n",
    "\n",
    "#### 1. 缺失值处理\n",
    "\n",
    "**检测方法**:\n",
    "- `isnull()`, `isna()`: 检测缺失\n",
    "- `info()`: 快速查看\n",
    "- 可视化: 热力图、条形图\n",
    "\n",
    "**处理策略**:\n",
    "- **删除**: `dropna()`\n",
    "- **固定值**: `fillna(0)`, `fillna('未知')`\n",
    "- **统计值**: 均值/中位数/众数\n",
    "- **前后向**: `ffill()`, `bfill()`\n",
    "- **插值**: `interpolate()`\n",
    "- **分组填充**: `groupby().transform()`\n",
    "\n",
    "**决策原则**:\n",
    "- 缺失 >50% → 删除列\n",
    "- 缺失 <5% → 删除行\n",
    "- 分类变量 → 众数或新类别\n",
    "- 数值变量 → 中位数（有异常）/均值（无异常）\n",
    "- 时间序列 → 插值或前后填充\n",
    "\n",
    "#### 2. 异常值处理\n",
    "\n",
    "**检测方法**:\n",
    "1. **业务规则法** ⭐推荐首选\n",
    "   - 基于业务知识定义合理范围\n",
    "   \n",
    "2. **3σ原则**\n",
    "   - 适用于正态分布\n",
    "   - 界限: μ ± 3σ\n",
    "\n",
    "3. **IQR法** ⭐推荐\n",
    "   - 不假设分布\n",
    "   - 界限: Q1-1.5×IQR, Q3+1.5×IQR\n",
    "   \n",
    "4. **Z-Score法**\n",
    "   - 标准化距离\n",
    "   - |Z| > 3 为异常\n",
    "\n",
    "**处理策略**:\n",
    "- **删除**: 确认错误且数量少\n",
    "- **边界替换**: `clip()` 限制极值\n",
    "- **统计值替换**: 中位数/均值\n",
    "- **保留标记**: 不确定时做标记\n",
    "\n",
    "### Excel vs Pandas 对比\n",
    "\n",
    "| 任务 | Excel | Pandas |\n",
    "|------|-------|--------|\n",
    "| 缺失值检查 | 手动查找空白 | `isnull().sum()` |\n",
    "| 缺失值填充 | 手动填充/Ctrl+D | `fillna()` 多种方法 |\n",
    "| 异常值检测 | 条件格式/排序 | IQR自动检测 |\n",
    "| 异常值处理 | 手动删除/替换 | `clip()`, 批量处理 |\n",
    "| 可视化 | 手动创建图表 | 一行代码生成 |\n",
    "| 批量处理 | 需要VBA | 原生支持 |\n",
    "\n",
    "### 最佳实践\n",
    "\n",
    "1. **先理解再处理**: 分析缺失/异常的原因和模式\n",
    "2. **保留原始数据**: 在副本上操作\n",
    "3. **记录处理过程**: 代码就是文档\n",
    "4. **验证结果**: 清洗前后对比统计量\n",
    "5. **业务优先**: 结合业务逻辑，不只看统计\n",
    "\n",
    "### 下节预告\n",
    "**第4讲: 数据格式规范与去重**\n",
    "- 数据类型转换\n",
    "- 日期时间处理\n",
    "- 字符串清洗\n",
    "- 去重策略\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 课后作业\n",
    "\n",
    "### 作业1: 缺失值处理实战\n",
    "给定一个包含缺失值的销售数据集，完成:\n",
    "1. 完整的缺失值检查报告\n",
    "2. 分析缺失模式（MCAR/MAR/MNAR）\n",
    "3. 对不同列选择合适的填充策略并说明理由\n",
    "4. 对比3种填充方法的效果\n",
    "5. 可视化缺失模式\n",
    "\n",
    "### 作业2: 异常值检测对比\n",
    "1. 用4种方法（业务规则/3σ/IQR/Z-Score）检测同一数据集的异常值\n",
    "2. 对比各方法检出的异常值数量和重叠情况\n",
    "3. 分析哪种方法最适合该数据\n",
    "4. 用3种策略处理异常值并对比效果\n",
    "5. 绘制完整的可视化报告\n",
    "\n",
    "### 作业3: 完整清洗流程\n",
    "提供一份包含多种脏数据的真实数据集，要求:\n",
    "1. 完整的数据质量评估报告\n",
    "2. 设计并实施清洗方案\n",
    "3. 记录每一步的处理逻辑\n",
    "4. 清洗前后对比分析\n",
    "5. 导出清洗后的数据和清洗日志\n",
    "\n",
    "### 作业4: 编写清洗函数\n",
    "编写一个通用的数据清洗函数包，包含:\n",
    "1. 缺失值自动处理函数（智能选择策略）\n",
    "2. 异常值自动检测和处理函数\n",
    "3. 数据质量评分函数\n",
    "4. 清洗报告生成函数\n",
    "5. 可以应用到任意数据集"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
